﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Tilver.Coder.Core;
using Tilver.Coder.Utility;
using System.Data;
using System.Data.SqlClient;

namespace Tilver.Coder.DBTextStyle
{
    /// <summary>
    /// SQLServer数据库的文本样式
    /// </summary>
    public class MSSqlTextStyle : DBTextStyleBase
    {
        public MSSqlTextStyle()
        {
            base.DataProviderType = DataProviderTypeEnum.MSSQL;
        }
        /// <summary>
        /// 定义参数变量的前缀申请
        /// </summary>
        const char _operator = '@';
        /// <summary>
        /// 获取一个表的完整名称
        /// </summary>
        /// <remarks>[dbo].[student]</remarks>
        /// <param name="owner">架构名</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public override string GetTableFullName(string owner, string tableName)
        {
            if (string.IsNullOrEmpty(tableName))
            {
                throw new ArgumentException("tableName  is null or empty");
            }
            if (string.IsNullOrEmpty(owner))
            {
                return string.Format("[{0}]", tableName);
            }
            else
            {
                return string.Format("[{0}].[{1}]", owner, tableName);
            }
        }
        /// <summary>
        /// 获取一个视图的完整名称
        /// </summary>
        /// <remarks>[dbo].[vStudentCourse]</remarks>
        /// <param name="owner">架构名称</param>
        /// <param name="viewName">视图名</param>
        /// <returns></returns>
        public override string GetViewFullName(string owner, string viewName)
        {
            if (string.IsNullOrEmpty(viewName))
            {
                throw new ArgumentException("viewName is null or empty");
            }
            if (string.IsNullOrEmpty(owner))
            {
                return string.Format("[{0}]", viewName);
            }
            else
            {
                return string.Format("[{0}].[{1}]", owner, viewName);
            }
        }

        public override string GetCommandFullName(string owner, string commandName)
        {
            if (string.IsNullOrEmpty(commandName))
            {
                throw new ArgumentException("commandName is null or empty");
            }
            if (string.IsNullOrEmpty(owner))
            {
                return string.Format("[{0}]", commandName);
            }
            else
            {
                return string.Format("[{0}].[{1}]", owner, commandName);
            }
        }

        /// <summary>
        /// 去除变量前缀
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public override string Clean(string value)
        {
            return value.TrimStart(_operator);
        }
        /// <summary>
        /// 获取列名
        /// </summary>
        /// <param name="columnName"></param>
        /// <returns></returns>
        public override string GetColumnName(string columnName)
        {
            return string.Format("[{0}]", Clean(columnName));
        }
        /// <summary>
        /// 获取参数名
        /// </summary>
        /// <param name="columnName">列名</param>
        /// <returns></returns>
        public override string GetParameterName(string columnName)
        {
            return string.Format("@{0}", Clean(columnName));
        }
        /// <summary>
        /// 获取参数名
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">列名</param>
        /// <returns></returns>
        public override string GetParameterName(string tableName, string columnName)
        {
            if (string.IsNullOrEmpty(tableName))
            {
                throw new ArgumentException("tableName is null or empty");
            }
            if (string.IsNullOrEmpty(columnName))
            {
                throw new ArgumentException("columnName is null or empty");
            }
            return string.Format("@{0}_{1}", tableName, columnName);
        }
        /// <summary>
        /// 转换成c#中的默认值
        /// </summary>
        /// <param name="dataObject"></param>
        /// <returns></returns>
        public override string GetCSharpDefaultValue(Core.CoderDataObject dataObject)
        {
            if (string.IsNullOrEmpty(dataObject.DefaultValue))
            {
                if (!dataObject.AllowDBNull && dataObject.CSharpType == "string")
                    return "string.Empty";
                else
                    return string.Empty;
            }
            else
            {
                switch (dataObject.DbType)
                {
                    case DbType.AnsiString:
                    case DbType.AnsiStringFixedLength:
                    case DbType.String:
                    case DbType.StringFixedLength:
                    case DbType.Xml:
                        return string.Format("\"{0}\"", dataObject.DefaultValue.TrimString("('", "')").TrimString("(N'", "')"));
                    case DbType.Date:
                    case DbType.DateTime:
                    case DbType.DateTime2:
                        string value = dataObject.DefaultValue.TrimString("(", ")");

                        if (value.Trim().ToLower() == "getdate()")
                            return "DateTime.Now";
                        else
                        {
                            value = value.Replace("(", "").Replace(")", "").Replace("'", "");

                            if (!value.IsDateTime())
                                throw new ArgumentException(string.Format("{0}的默认值({1})不是合法的时间", dataObject.Name, dataObject.DefaultValue));
                            return string.Format("DateTime.Parse(\"{0}\")", value);
                        }
                    case DbType.Boolean:
                        return (dataObject.DefaultValue.TrimString("((", "))") == "1") ? "true" : "false";
                    case DbType.Byte:
                    case DbType.Currency:
                    case DbType.Decimal:
                    case DbType.Double:
                    case DbType.Int16:
                    case DbType.Int32:
                    case DbType.Int64:
                    case DbType.SByte:
                    case DbType.Single:
                    case DbType.UInt16:
                    case DbType.UInt32:
                    case DbType.UInt64:
                    case DbType.VarNumeric:
                        return string.Format("({0}){1}", dataObject.CSharpType, dataObject.DefaultValue.TrimString("((", "))"));
                    case DbType.Guid:
                        string guidValue = dataObject.DefaultValue.TrimString("(", ")");

                        if (guidValue.ToLower() == "newid()")
                            return "Guid.NewGuid()";
                        else
                        {
                            guidValue = guidValue.Replace("(", "").Replace(")", "").Replace("'", "");

                            if (!guidValue.IsGuid())
                                throw new ArgumentException(string.Format("{0}的默认值({1})不是合法的guid", dataObject.Name, dataObject.DefaultValue));
                            return string.Format("Guid.Parse(\"{0}\")", guidValue);
                        }
                    default:
                        throw new ArgumentException(string.Format("{0}的默认值({1})无效", dataObject.Name, dataObject.DefaultValue));
                }
            }
        }
        /// <summary>
        /// 执行sql语句，返回数据
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="commandText"></param>
        /// <param name="commandType"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public override System.Data.DataSet ExecuteSql(string connectionString, string commandText, System.Data.CommandType commandType, IEnumerable<CoderParameter> paras)
        {
            DataSet result = new DataSet();
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(commandText, con))
                {

                    cmd.CommandType = commandType;

                    if (paras != null)
                    {
                        foreach (var para in paras)
                        {
                            SqlParameter newSqlPara = cmd.CreateParameter();
                            newSqlPara.ParameterName = para.Name;
                            newSqlPara.DbType = para.DbType;
                            newSqlPara.Direction = para.Direction;
                            newSqlPara.Value = para.Value == null ? DBNull.Value : (object)para.Value;
                            cmd.Parameters.Add(newSqlPara);
                        }
                    }

                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        adapter.Fill(result);
                        return result;
                    }
                }
            }
        }

        public override DataSet GetTopData(string connectionString, Core.IMOGenerable mo, int count = 100)
        {
            return ExecuteSql(connectionString, string.Format("SELECT TOP {0} * FROM {1}", count, GetTableFullName(mo.Owner, mo.Name)), CommandType.Text, null);
        }

        public override void WriteTopSql(int tabs, StringBuilder response)
        {
            response.AppendTabLine(tabs, "sql.AppendFormat(format.ToString(), (top > 0) ? string.Format(\"TOP {0}\", top) : string.Empty);");
        }

        public override string GetInsertIdentitySql()
        {
            return ";SELECT SCOPE_IDENTITY();";
        }
    }
}
